package com.share.common.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

@Slf4j
public class ExcelUtil {
    private static final String XLS = ".xls";
    private static final String XLSX = ".xlsx";

    /**
     * 读取Excel中文件的特定的表，返回数据对象
     *
     * @param filePath  文件路径
     * @param sheetName 表格名字（索引）
     * @return
     */

    public static List<Map<String, String>> readExcel(String filePath, String sheetName) {
        return readExcel(filePath, sheetName, null, null);
    }

    public static List<Map<String, String>> readExcel(String filePath, String sheetName, Integer startLineIndex) {
        return readExcel(filePath, sheetName, startLineIndex, null);
    }

    //读取第一个sheet的[0,0]是标题
    public static List<String> readExcelSamp(String filePath) {
        List<String> reList = new LinkedList<>();
        List<Map<String, String>> listMaps = readExcel(filePath, null, null, 0);
        for (Map<String, String> tMap : listMaps) {
            Collection<String> values = tMap.values();
            reList.addAll(values);
        }
        return reList;
    }

    private static List<Map<String, String>> readExcel(String filePath, String sheetName, Integer startLineIndex, Integer onlyColIndex) {
        Workbook workbook = null;
        List<Map<String, String>> resultList = new ArrayList<>();
        try {
            String fileType = filePath.substring(filePath.lastIndexOf("."));
            workbook = getWorkbook(filePath, fileType);
            if (workbook == null) {
                log.info("获取workbook对象失败");
                return null;
            }
            resultList = analysisExcel(workbook, sheetName, startLineIndex, onlyColIndex);
            return resultList;
        } catch (Exception e) {
            log.error("读取Excel文件失败" + filePath + "错误信息", e);
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：", e);
                return null;
            }
        }
    }

    /**
     * 根据文件后缀获取对应Workbook对象
     *
     * @param filePath
     * @param fileType
     * @return
     */
    private static Workbook getWorkbook(String filePath, String fileType) {
        Workbook workbook = null;
        FileInputStream fileInputStream = null;
        try {
            File excelFile = new File(filePath);
            if (!excelFile.exists()) {
                log.info(filePath + "文件不存在");
                return null;
            }
            fileInputStream = new FileInputStream(excelFile);
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(fileInputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(fileInputStream);
            }
        } catch (Exception e) {
            log.error("获取文件失败", e);
        } finally {
            try {
                if (null != fileInputStream) {
                    fileInputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：", e);
                return null;
            }
        }
        return workbook;
    }

    /**
     * 解析Excel文件中特定的表，返回数据对象
     *
     * @param workbook       生成对应的excel处理
     * @param sheetName      表格名字（索引）  不给值读取第一页
     * @param startLineIndex 读取的起始行 默认从0开始
     * @param onlyColIndex   为空值时输出所有列，非空时输出特定的列
     * @return
     */
    private static List<Map<String, String>> analysisExcel(Workbook workbook, String sheetName, Integer startLineIndex, Integer onlyColIndex) {
        List<Map<String, String>> dataList = new LinkedList<>();
        int sheetIndex = -1;
        if (StringUtils.isEmpty(sheetName)) {
            sheetIndex = 0;
        } else {
            int sheetCount = workbook.getNumberOfSheets();//获取一个Excel中sheet数量
            for (int i = 0; i < sheetCount; i++) {
                if (sheetName.equals(workbook.getSheetName(i))) {
                    sheetIndex = i;
                    break;
                }
            }
        }
        if (sheetIndex >= 0) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            dataList = analysisOneSheet(sheet, startLineIndex, onlyColIndex);
        }

        return dataList;
    }

    private static List<Map<String, String>> analysisOneSheet(Sheet sheet, Integer startLineIndex, Integer onlyColIndex) {
        List<Map<String, String>> dataList = new ArrayList<>();
        int firstRowCount = sheet.getFirstRowNum();//获取第一行的序号
        if (startLineIndex != null) {
            firstRowCount = startLineIndex;
        }
        Row firstRow = sheet.getRow(firstRowCount);
        int cellCount = firstRow.getLastCellNum();//获取列数

        List<String> mapKey = new ArrayList<>();

        //获取表头信息，放在List中备用
        if (firstRow == null) {
            log.info("解析Excel失败，在第一行没有读取到任何数据！");
        } else {
            for (int i1 = 0; i1 < cellCount; i1++) {
                mapKey.add(firstRow.getCell(i1).toString());
            }
        }

        //解析每一行数据，构成数据对象
        int rowStart = firstRowCount + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();//获取总行数
        for (int j = rowStart; j < rowEnd; j++) {
            Row row = sheet.getRow(j);//获取对应的row对象

            if (row == null) {
                continue;
            }

            Map<String, String> dataMap = new HashMap<>();
            if (onlyColIndex != null) {
                //只要一列
                Cell cell = row.getCell(onlyColIndex);
                String cellVal = getCellVal(cell);
                dataMap.put(mapKey.get(onlyColIndex), cellVal);
            } else {
                //将每一行数据转化为一个Map对象
                dataMap = convertRowToData(row, cellCount, mapKey);
            }
            dataList.add(dataMap);
        }
        return dataList;
    }

    /**
     * 将每一行数据转化为一个Map对象
     *
     * @param row       行对象
     * @param cellCount 列数
     * @param mapKey    表头Map
     * @return
     */
    private static Map<String, String> convertRowToData(Row row, int cellCount, List<String> mapKey) {
        if (mapKey == null) {
            log.info("没有表头信息");
            return null;
        }
        Map<String, String> resultMap = new HashMap<>();
        Cell cell = null;
        for (int i = 0; i < cellCount; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                resultMap.put(mapKey.get(i), "");
            } else {
                resultMap.put(mapKey.get(i), getCellVal(cell));
            }
        }
        return resultMap;
    }

    /**
     * 获取单元格的值
     *
     * @param cel
     * @return
     */
    private static String getCellVal(Cell cel) {
        if (cel.getCellType() == CellType.STRING) {
            return cel.getRichStringCellValue().getString();
        }
        if (cel.getCellType() == CellType.NUMERIC) {
            return cel.getNumericCellValue() + "";
        }
        if (cel.getCellType() == CellType.BOOLEAN) {
            return cel.getBooleanCellValue() + "";
        }
        if (cel.getCellType() == CellType.FORMULA) {
            return cel.getCellFormula() + "";
        }
        return cel.toString();
    }
}
